package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.DailyProgram;
import bg.cinema_system.utils.Constants;

public class DailyProgramListDAO {

	// Connect to DB and get DailyProgram list
	public static List<DailyProgram> getDailyProgramList(long interval) throws SQLException {
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException("Class DailyProgramListDAO: Can't get database connection");
		}

		String sqlStatement = "";

		if (interval == 0) {
			sqlStatement = "SELECT projections.projection_id, movies.movie_id, movies.name, projections.projection_start "
					+ "FROM projections "
					+ "INNER JOIN movies "
					+ "ON projections.fk_movie_id = movies.movie_id "
					+ "WHERE projections.projection_start >= NOW() + INTERVAL ? MINUTE "
					+ "AND projections.projection_start < CURDATE() + INTERVAL '1' DAY "
					+ "ORDER BY movies.name";
		} else {
			sqlStatement = "SELECT projections.projection_id, movies.movie_id, movies.name, projections.projection_start "
					+ "FROM projections "
					+ "INNER JOIN movies "
					+ "ON projections.fk_movie_id = movies.movie_id "
					+ "WHERE projections.projection_start >= CURDATE() + INTERVAL ? DAY "
					+ "AND projections.projection_start < CURDATE() + INTERVAL ? DAY "
					+ "ORDER BY movies.name";
		}

		PreparedStatement ps = null;
		ResultSet result = null;
		List<DailyProgram> dailyProgramList = new ArrayList<DailyProgram>();
		try {
			ps = dbConnection.prepareStatement(sqlStatement);
			if (interval == 0) {
				ps.setLong(1, Constants.EXPIRE_BEFORE_PROJECTION);
			} else {
				ps.setLong(1, interval);
				ps.setLong(2, interval + 1);
			}
			
			// Get customer data from database
			result = ps.executeQuery();
			
			while (result.next()) {
				// Create Program object and assign it to null (have to check if
				// such already exists in the list)
				DailyProgram program = null;
				
				// Create an empty movie_start and movie_projection_id arrays with
				// 15 items each
				String[] movie_start = new String[15];
				String[] movie_projection_id = new String[15];
				
				// Check if the Program object is already created and if so assign
				// it to "program" object
				for (int i = 0; i < dailyProgramList.size(); i++) {
					if (dailyProgramList.get(i).getName()
							.equals(result.getString("name"))) {
						program = dailyProgramList.get(i);
						
						movie_start = dailyProgramList.get(i).getMovie_start();
						movie_projection_id = dailyProgramList.get(i)
								.getMovie_projection_id();
						
						dailyProgramList.remove(i);
						break;
					}
				}
				
				// If the Program object is not in the list, assign empty values
				if (program == null) {
					program = new DailyProgram();
					
					for (int i = 0; i < 15; i++) {
						movie_start[i] = "";
						movie_projection_id[i] = "";
					}
				}
				
				// Get the "start time" and the "projection id" of the projection of
				// the movie
				Date tempProjectionStart = result.getTime("projection_start");
				Integer hour = null;
				String start_time = "";
				String projection_id = "" + result.getLong("projection_id");
				
				// Check if the start time is valid and if so, get the hour and the
				// start_time (which will be visible in the table)
				try {
					hour = Integer.parseInt(tempProjectionStart.toString()
							.substring(0,
									tempProjectionStart.toString().indexOf(":")));
					start_time = tempProjectionStart.toString().substring(0,
							tempProjectionStart.toString().lastIndexOf(":"));
				} catch (Exception e) {
				}
				
				// Check on which position to put the "start time" and the
				// "projection id" in the program table
				if (hour != null) {
					if (hour >= 0 && hour < 1) {
						movie_projection_id[0] = projection_id;
						movie_start[0] = start_time;
					}
					if (hour >= 10 && hour < 11) {
						movie_projection_id[1] = projection_id;
						movie_start[1] = start_time;
					}
					if (hour >= 11 && hour < 12) {
						movie_projection_id[2] = projection_id;
						movie_start[2] = start_time;
					}
					if (hour >= 12 && hour < 13) {
						movie_projection_id[3] = projection_id;
						movie_start[3] = start_time;
					}
					if (hour >= 13 && hour < 14) {
						movie_projection_id[4] = projection_id;
						movie_start[4] = start_time;
					}
					if (hour >= 14 && hour < 15) {
						movie_projection_id[5] = projection_id;
						movie_start[5] = start_time;
					}
					if (hour >= 15 && hour < 16) {
						movie_projection_id[6] = projection_id;
						movie_start[6] = start_time;
					}
					if (hour >= 16 && hour < 17) {
						movie_projection_id[7] = projection_id;
						movie_start[7] = start_time;
					}
					if (hour >= 17 && hour < 18) {
						movie_projection_id[8] = projection_id;
						movie_start[8] = start_time;
					}
					if (hour >= 18 && hour < 19) {
						movie_projection_id[9] = projection_id;
						movie_start[9] = start_time;
					}
					if (hour >= 19 && hour < 20) {
						movie_projection_id[10] = projection_id;
						movie_start[10] = start_time;
					}
					if (hour >= 20 && hour < 21) {
						movie_projection_id[11] = projection_id;
						movie_start[11] = start_time;
					}
					if (hour >= 21 && hour < 22) {
						movie_projection_id[12] = projection_id;
						movie_start[12] = start_time;
					}
					if (hour >= 22 && hour < 23) {
						movie_projection_id[13] = projection_id;
						movie_start[13] = start_time;
					}
					if (hour >= 23 && hour < 24) {
						movie_projection_id[14] = projection_id;
						movie_start[14] = start_time;
					}
				}
				
				// Assign the variables to the "program" object
				program.setMovie_id("" + result.getLong("movie_id"));
				program.setName(result.getString("name"));
				program.setMovie_start(movie_start);
				program.setMovie_projection_id(movie_projection_id);
				program.setProgramInfo(Constants.INFORMATION_OK);
				
				// Store all data into a List
				dailyProgramList.add(program);
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}

		// Check if there are any films for this day
		if (dailyProgramList.isEmpty()) {
			DailyProgram program = new DailyProgram();
			program.setProgramInfo(Constants.INFORMATION_KO);

			dailyProgramList.add(program);
		}

		return dailyProgramList;
	}
}